﻿Imports Control_Vuelos_BE
Imports System.Data.SqlClient

Public Class V_ReservaADO
    ReadOnly MiConexion As New ConexionADO()
    ReadOnly cnx As New SqlConnection(MiConexion.GetCnx)
    Dim cmd As New SqlCommand
    Dim lExito As Boolean

    Public Function pr_BuscarReserva(ByVal sPas As String, ByVal sRes As String, ByVal dFch As Date) As String
        Dim sfec As String = dFch.ToString
        Dim dtr As SqlDataReader
        Dim sMensaje As String = ""
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("select re.CodReserva,re.FchReserva,mv.desMotivo as Motivo FROM dbo.V_Reserva re INNER JOIN dbo.M_MotivoViaje mv ON re.MotivoViaje=mv.codMotivo WHERE re.CodPasajero = {0} AND re.FchViaje = '{1}' AND re.CodReserva <> {2}", sPas, Strings.Left(sfec, 10), sRes)
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                sfec = dtr(1).ToString
                sMensaje = String.Format("Reserva: {0}{1}Fecha: {2}{1}Motivo: {3}", dtr(0).ToString, vbCrLf, Strings.Left(sfec, 10), dtr(2).ToString.Trim)
            End If
        Catch x As SqlException
            MsgBox("V_ReservaADO (pr_BuscarReserva) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
        End Try
        Return sMensaje
    End Function

    Public Function pr_CargarLugarIngreso(ByVal sUsuario As String) As String
        Dim dtr As SqlDataReader
        Dim sLugar As String = ""
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT TOP 1 CodLugar FROM dbo.M_Lugar WHERE TipLugar='L' AND CodLugar IN (SELECT DISTINCT CodLugar FROM dbo.Usuario_Lugar WHERE (coduser='{0}'))", sUsuario.Trim)
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                sLugar = dtr(0)
            End If
        Catch x As SqlException
            MsgBox("V_ReservaADO (pr_CargarLugarIngreso) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
        End Try
        Return sLugar
    End Function

    Public Function pr_CargarPrioridad(ByVal sTipo As String) As String
        Dim dtr As SqlDataReader
        Dim sPrioridad As String = ""
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT codPrioridad FROM dbo.M_TipoPasajero WHERE (CodTipoPasajero='{0}')", sTipo)
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                sPrioridad = dtr(0)
            End If
        Catch x As SqlException
            MsgBox("V_ReservaADO (pr_CargarPrioridad) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
        End Try
        Return sPrioridad
    End Function

    Public Function pr_Cargar_ReservaPendiente(ByVal sGpoReserva As String, ByVal sEstReserva As String, ByVal sLugReserva As String) As DataTable
        Dim dt As New DataTable
        Dim sql As String = String.Format("SELECT re.CodReserva,pa.AppPasajero,pa.ApmPasajero,pa.NomPasajero,pa.TlfPasajero,pa.RpcPasajero,pa.MailPasajero,pa.NacionalidadPasajero,re.FchReserva,re.TipPasajero,re.NomContacto,re.FchViaje,re.PsoPasajero,re.NroPrioridad,re.MotivoViaje,re.CodAnalisis,re.CodCentroCosto,re.PctCentroCosto,re.CodCentroCosto2,0 as PctCentroCosto2,re.PsoEquipajePasajero,re.EstadoReserva,re.FlgRequisitos,re.FchRetorno,re.Flgpermiso,re.CodPasajero,re.CodlugarReserva,re.NroBoleta,re.ObsReserva,pa.NroDocumentoId,CAST(0 as int) as nrosec,CAST(0 as int) as FlgLock,CAST(0 as bit) as FlgResponsable,CASE re.TipPasajero WHEN '1' THEN  'Personal Empresa' ELSE an.Desanl END AS Desanl FROM dbo.v_Reserva re INNER JOIN m_pasajero pa ON re.codPasajero=pa.COdpasajero INNER JOIN Analisis an ON re.CodAnalisis=an.Codanl WHERE (re.tippasajero IN (SELECT codTipoPasajero FROM dbo.M_TipoPasajero WHERE GpoTipoPasajero='{0}')) AND re.estadoreserva='{1}' AND re.CodLugarReserva='{2}' ORDER BY re.NroPrioridad DESC", sGpoReserva, sEstReserva, sLugReserva)
        Try
            cnx.Open()
            Using da As New SqlDataAdapter(sql, cnx)
                da.Fill(dt)
            End Using
            cnx.Close()
            Return dt
        Catch myException As Exception
            MsgBox("V_ReservaADO (prCargar_ReservaPendiente) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_CrearReserva(ByVal rBE As V_ReservaBE) As Boolean
        cmd.Connection = cnx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_InsertarReserva"
        Try
            With rBE
                cmd.Parameters.Add(New SqlParameter("@pdFchReserva", SqlDbType.DateTime))
                cmd.Parameters("@pdFchReserva").Value = .FchReserva
                cmd.Parameters.Add(New SqlParameter("@psTipPasajero", SqlDbType.VarChar, 1))
                cmd.Parameters("@psTipPasajero").Value = .TipPasajero
                cmd.Parameters.Add(New SqlParameter("@psNomContacto", SqlDbType.VarChar, 7))
                cmd.Parameters("@psNomContacto").Value = .NomContacto
                cmd.Parameters.Add(New SqlParameter("@pdFchViaje", SqlDbType.DateTime))
                cmd.Parameters("@pdFchViaje").Value = .FchViaje
                cmd.Parameters.Add(New SqlParameter("@pnPsoPasajero", SqlDbType.Decimal))
                cmd.Parameters("@pnPsoPasajero").Value = .PsoPasajero
                cmd.Parameters.Add(New SqlParameter("@psNroPrioridad", SqlDbType.VarChar, 1))
                cmd.Parameters("@psNroPrioridad").Value = .NroPrioridad
                cmd.Parameters.Add(New SqlParameter("@psMotivoViaje", SqlDbType.VarChar, 3))
                cmd.Parameters("@psMotivoViaje").Value = .MotivoViaje
                cmd.Parameters.Add(New SqlParameter("@psCodAnalisis", SqlDbType.VarChar, 7))
                cmd.Parameters("@psCodAnalisis").Value = .CodAnalisis
                cmd.Parameters.Add(New SqlParameter("@psCodComunidad", SqlDbType.VarChar, 7))
                cmd.Parameters("@psCodComunidad").Value = IIf(.CodComunidad Is String.Empty, "0000000", .CodComunidad)
                cmd.Parameters.Add(New SqlParameter("@psCodCentroCosto", SqlDbType.VarChar, 7))
                cmd.Parameters("@psCodCentroCosto").Value = .CodCentroCosto
                cmd.Parameters.Add(New SqlParameter("@pnPsoEquipajePasajero", SqlDbType.Decimal))
                cmd.Parameters("@pnPsoEquipajePasajero").Value = .PsoEquipajePasajero
                cmd.Parameters.Add(New SqlParameter("@psEstadoReserva", SqlDbType.VarChar, 1))
                cmd.Parameters("@psEstadoReserva").Value = .EstadoReserva
                cmd.Parameters.Add(New SqlParameter("@plFlgRequisitos", SqlDbType.Bit))
                cmd.Parameters("@plFlgRequisitos").Value = .FlgRequisitos
                cmd.Parameters.Add(New SqlParameter("@pdFchRetorno", SqlDbType.DateTime))
                cmd.Parameters("@pdFchRetorno").Value = .FchRetorno
                cmd.Parameters.Add(New SqlParameter("@plFlgPermiso", SqlDbType.Bit))
                cmd.Parameters("@plFlgPermiso").Value = .FlgPermiso
                If .CodPasajero > 0 Then
                    cmd.Parameters.Add(New SqlParameter("@pnCodPasajero", SqlDbType.Int))
                    cmd.Parameters("@pnCodPasajero").Value = .CodPasajero
                End If
                cmd.Parameters.Add(New SqlParameter("@psCodLugarReserva", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarReserva").Value = .CodLugarReserva
                cmd.Parameters.Add(New SqlParameter("@psNroBoleta", SqlDbType.VarChar, 20))
                cmd.Parameters("@psNroBoleta").Value = .NroBoleta
                cmd.Parameters.Add(New SqlParameter("@psObsReserva", SqlDbType.Text))
                cmd.Parameters("@psObsReserva").Value = .ObsReserva
                cmd.Parameters.Add(New SqlParameter("@pnPctCentroCosto", SqlDbType.Int))
                cmd.Parameters("@pnPctCentroCosto").Value = .PctCentroCosto
                cmd.Parameters.Add(New SqlParameter("@psCodCentroCosto2", SqlDbType.VarChar, 7))
                cmd.Parameters("@psCodCentroCosto2").Value = .CodCentroCosto2
                cmd.Parameters.Add(New SqlParameter("@plFlgSctrSalud", SqlDbType.Bit))
                cmd.Parameters("@plFlgSctrSalud").Value = .FlgSctrSalud
                cmd.Parameters.Add(New SqlParameter("@pdFchSctrSalud", SqlDbType.DateTime))
                cmd.Parameters("@pdFchSctrSalud").Value = IIf(.FlgSctrSalud = True, DateTime.Now, DBNull.Value)
                cmd.Parameters.Add(New SqlParameter("@plFlgSctrPension", SqlDbType.Bit))
                cmd.Parameters("@plFlgSctrPension").Value = .FlgSctrPension
                cmd.Parameters.Add(New SqlParameter("@pdFchSctrPension", SqlDbType.DateTime))
                cmd.Parameters("@pdFchSctrPension").Value = IIf(.FlgSctrPension = True, DateTime.Now, DBNull.Value)
                cmd.Parameters.Add(New SqlParameter("@plFlgExamenAltitud", SqlDbType.Bit))
                cmd.Parameters("@plFlgExamenAltitud").Value = .FlgExamenAltitud
                cmd.Parameters.Add(New SqlParameter("@pdFchExamenAltitud", SqlDbType.DateTime))
                cmd.Parameters("@pdFchExamenAltitud").Value = IIf(.FlgExamenAltitud = True, DateTime.Now, DBNull.Value)
                cmd.Parameters.Add(New SqlParameter("@plFlgAsumeMarsa", SqlDbType.Bit))
                cmd.Parameters("@plFlgAsumeMarsa").Value = .FlgAsumeMarsa
                cmd.Parameters.Add(New SqlParameter("@plFlgPrestamo", SqlDbType.Bit))
                cmd.Parameters("@plFlgPrestamo").Value = .FlgPrestamo
                cmd.Parameters.Add(New SqlParameter("@plFlgResRetorno", SqlDbType.Bit))
                cmd.Parameters("@plFlgResRetorno").Value = .FlgResRetorno
                cmd.Parameters.Add(New SqlParameter("@plFlgNew", SqlDbType.Bit))
                cmd.Parameters("@plFlgNew").Value = .FlgNew
                cmd.Parameters.Add(New SqlParameter("@plFlgResPasajero", SqlDbType.Bit))
                cmd.Parameters("@plFlgResPasajero").Value = .FlgResPasajero
                cmd.Parameters.Add(New SqlParameter("@psCodCreadopor", SqlDbType.Char, 20))
                cmd.Parameters("@psCodCreadopor").Value = .CodCreadopor
                cmd.Parameters.Add(New SqlParameter("@pdFchCreado", SqlDbType.DateTime))
                cmd.Parameters("@pdFchCreado").Value = .FchCreado
            End With
            cnx.Open()
            cmd.ExecuteNonQuery()
            lExito = True
        Catch x As SqlException
            MsgBox("V_ReservaADO (pr_crearReserva) --> " & x.Message, MsgBoxStyle.Critical)
            lExito = False
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return lExito
    End Function

    Public Function pr_EliminarReserva(ByVal sCodr As Integer) As Boolean
        cmd.Connection = cnx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_EliminarReserva"
        Try
            cmd.Parameters.Add(New SqlParameter("@pnCodReserva", SqlDbType.Int))
            cmd.Parameters("@pnCodReserva").Value = sCodr
            cnx.Open()
            cmd.ExecuteNonQuery()
            lExito = True
        Catch x As SqlException
            MsgBox("V_ReservaADO (pr_EliminarReserva) --> " & x.Message, MsgBoxStyle.Critical)
            lExito = False
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return lExito
    End Function

    Public Function pr_GetReserva() As DataTable
        Using dts As New DataSet()
            Dim ada As SqlDataAdapter
            Try
                cmd.Connection = cnx
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "sp_ListadeViajesRealizados"
                ada = New SqlDataAdapter(cmd)
                ada.Fill(dts, "D_Manifiesto")
            Catch ex As Exception
                Throw New Exception(ex.Message)
            End Try
            Return dts.Tables("D_Manifiesto")
        End Using
    End Function

    Public Function pr_Listar_Reserva(ByVal sUsuario As String, ByVal sFiltro As String) As DataTable
        Dim dt As New DataTable
        Dim sql As String = "SELECT r.CodReserva,r.FchReserva,r.FchViaje,r.FchRetorno,lr.NomLugar as Lugar,p.NroDocumentoId as dni,p.AppPasajero+' '+p.ApmPasajero+', '+p.NomPasajero as Pasajero,r.CodAnalisis,a.Desanl,p.CodLugarResidencia,lr2.NomLugar, " & _
                            "CASE WHEN r.EstadoReserva = '1' THEN 'Stand By' WHEN r.EstadoReserva = '2' THEN 'Habilitado'  WHEN r.EstadoReserva = '3' THEN 'Programado' WHEN r.EstadoReserva = '4' THEN 'Arrivado' WHEN r.EstadoReserva = '5' THEN 'Perdida de vuelo' WHEN r.EstadoReserva = '9' THEN 'Anulado' ELSE 'No hay reserva' END EstadoReserva FROM dbo.V_Reserva r " & _
                            "INNER JOIN dbo.M_Pasajero p ON r.CodPasajero=p.CodPasajero " & _
                            "INNER JOIN dbo.M_lugar lr ON r.CodLugarReserva=lr.CodLugar  " & _
                            "INNER JOIN dbo.Analisis a ON r.CodAnalisis = a.codanl  " & _
                            "LEFT JOIN (SELECT CodLugar,NomLugar FROM dbo.M_Lugar WHERE TipLugar='R') lr2 ON p.CodLugarResidencia=lr2.CodLugar " & _
                            "WHERE (r.CodLugarReserva IN (SELECT DISTINCT CodLugar FROM dbo.Usuario_Lugar WHERE (coduser='" & sUsuario & "')))" & sFiltro
        Try
            cnx.Open()
            Using da As New SqlDataAdapter(sql, cnx)
                da.Fill(dt)
            End Using
            cnx.Close()
            Return dt
        Catch myException As Exception
            MsgBox("V_ReservaADO (pr_Listar_Reserva) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ListarReservasBuscar() As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Lista_Reservas_Buscar", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("Reservas")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("V_ReservaADO (pr_ListarReservasBuscar) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_ModificarReserva(ByVal rBE As V_ReservaBE) As Boolean
        cmd.Connection = cnx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_ModificarReserva"
        Try
            With rBE
                cmd.Parameters.Add(New SqlParameter("@pnCodReserva", SqlDbType.Int))
                cmd.Parameters("@pnCodReserva").Value = .CodReserva
                cmd.Parameters.Add(New SqlParameter("@pdFchReserva", SqlDbType.DateTime))
                cmd.Parameters("@pdFchReserva").Value = .FchReserva
                cmd.Parameters.Add(New SqlParameter("@psTipPasajero", SqlDbType.VarChar, 1))
                cmd.Parameters("@psTipPasajero").Value = .TipPasajero
                cmd.Parameters.Add(New SqlParameter("@psNomContacto", SqlDbType.VarChar, 7))
                cmd.Parameters("@psNomContacto").Value = .NomContacto
                cmd.Parameters.Add(New SqlParameter("@pdFchViaje", SqlDbType.DateTime))
                cmd.Parameters("@pdFchViaje").Value = .FchViaje
                cmd.Parameters.Add(New SqlParameter("@pnPsoPasajero", SqlDbType.Decimal))
                cmd.Parameters("@pnPsoPasajero").Value = .PsoPasajero
                cmd.Parameters.Add(New SqlParameter("@psNroPrioridad", SqlDbType.VarChar, 1))
                cmd.Parameters("@psNroPrioridad").Value = .NroPrioridad
                cmd.Parameters.Add(New SqlParameter("@psMotivoViaje", SqlDbType.VarChar, 3))
                cmd.Parameters("@psMotivoViaje").Value = .MotivoViaje
                cmd.Parameters.Add(New SqlParameter("@psCodAnalisis", SqlDbType.VarChar, 7))
                cmd.Parameters("@psCodAnalisis").Value = .CodAnalisis
                cmd.Parameters.Add(New SqlParameter("@psCodComunidad", SqlDbType.VarChar, 7))
                cmd.Parameters("@psCodComunidad").Value = IIf(.CodComunidad Is String.Empty, DBNull.Value, .CodComunidad)
                cmd.Parameters.Add(New SqlParameter("@psCodCentroCosto", SqlDbType.VarChar, 7))
                cmd.Parameters("@psCodCentroCosto").Value = .CodCentroCosto
                cmd.Parameters.Add(New SqlParameter("@pnPsoEquipajePasajero", SqlDbType.Decimal))
                cmd.Parameters("@pnPsoEquipajePasajero").Value = .PsoEquipajePasajero
                cmd.Parameters.Add(New SqlParameter("@psEstadoReserva", SqlDbType.VarChar, 1))
                cmd.Parameters("@psEstadoReserva").Value = .EstadoReserva
                cmd.Parameters.Add(New SqlParameter("@plFlgRequisitos", SqlDbType.Bit))
                cmd.Parameters("@plFlgRequisitos").Value = .FlgRequisitos
                cmd.Parameters.Add(New SqlParameter("@pdFchRetorno", SqlDbType.DateTime))
                cmd.Parameters("@pdFchRetorno").Value = .FchRetorno
                cmd.Parameters.Add(New SqlParameter("@plFlgPermiso", SqlDbType.Bit))
                cmd.Parameters("@plFlgPermiso").Value = .FlgPermiso
                cmd.Parameters.Add(New SqlParameter("@pnCodPasajero", SqlDbType.Int))
                cmd.Parameters("@pnCodPasajero").Value = .CodPasajero
                cmd.Parameters.Add(New SqlParameter("@psCodLugarReserva", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarReserva").Value = .CodLugarReserva
                cmd.Parameters.Add(New SqlParameter("@psNroBoleta", SqlDbType.VarChar, 20))
                cmd.Parameters("@psNroBoleta").Value = .NroBoleta
                cmd.Parameters.Add(New SqlParameter("@psObsReserva", SqlDbType.Text))
                cmd.Parameters("@psObsReserva").Value = .ObsReserva
                cmd.Parameters.Add(New SqlParameter("@pnPctCentroCosto", SqlDbType.Int))
                cmd.Parameters("@pnPctCentroCosto").Value = .PctCentroCosto
                cmd.Parameters.Add(New SqlParameter("@psCodCentroCosto2", SqlDbType.VarChar, 7))
                cmd.Parameters("@psCodCentroCosto2").Value = .CodCentroCosto2
                Dim nyearSalud As Integer = Year(.FchSctrSalud)
                Dim nyearPension As Integer = Year(.FchSctrPension)
                Dim nyearExamen As Integer = Year(.FchExamenAltitud)
                cmd.Parameters.Add(New SqlParameter("@plFlgSctrSalud", SqlDbType.Bit))
                cmd.Parameters("@plFlgSctrSalud").Value = .FlgSctrSalud
                cmd.Parameters.Add(New SqlParameter("@pdFchSctrSalud", SqlDbType.DateTime))
                cmd.Parameters("@pdFchSctrSalud").Value = IIf(.FlgSctrSalud = True, IIf(nyearSalud > 2012, .FchSctrSalud, DateTime.Now), DBNull.Value)
                cmd.Parameters.Add(New SqlParameter("@plFlgSctrPension", SqlDbType.Bit))
                cmd.Parameters("@plFlgSctrPension").Value = .FlgSctrPension
                cmd.Parameters.Add(New SqlParameter("@pdFchSctrPension", SqlDbType.DateTime))
                cmd.Parameters("@pdFchSctrPension").Value = IIf(.FlgSctrPension = True, IIf(nyearPension > 2012, .FchSctrPension, DateTime.Now), DBNull.Value)
                cmd.Parameters.Add(New SqlParameter("@plFlgExamenAltitud", SqlDbType.Bit))
                cmd.Parameters("@plFlgExamenAltitud").Value = .FlgExamenAltitud
                cmd.Parameters.Add(New SqlParameter("@pdFchExamenAltitud", SqlDbType.DateTime))
                cmd.Parameters("@pdFchExamenAltitud").Value = IIf(.FlgExamenAltitud = True, IIf(nyearExamen > 2012, .FchExamenAltitud, DateTime.Now), DBNull.Value)
                cmd.Parameters.Add(New SqlParameter("@plFlgAsumeMarsa", SqlDbType.Bit))
                cmd.Parameters("@plFlgAsumeMarsa").Value = .FlgAsumeMarsa
                cmd.Parameters.Add(New SqlParameter("@plFlgPrestamo", SqlDbType.Bit))
                cmd.Parameters("@plFlgPrestamo").Value = .FlgPrestamo
                cmd.Parameters.Add(New SqlParameter("@plFlgResRetorno", SqlDbType.Bit))
                cmd.Parameters("@plFlgResRetorno").Value = .FlgResRetorno
                cmd.Parameters.Add(New SqlParameter("@plFlgNew", SqlDbType.Bit))
                cmd.Parameters("@plFlgNew").Value = .FlgNew
                cmd.Parameters.Add(New SqlParameter("@plFlgResPasajero", SqlDbType.Bit))
                cmd.Parameters("@plFlgResPasajero").Value = .FlgResPasajero
                cmd.Parameters.Add(New SqlParameter("@psCodModificadopor", SqlDbType.Char, 20))
                cmd.Parameters("@psCodModificadopor").Value = .CodModificadopor
                cmd.Parameters.Add(New SqlParameter("@pdFchModificado", SqlDbType.DateTime))
                cmd.Parameters("@pdFchModificado").Value = .FchModificado
            End With
            cnx.Open()
            cmd.ExecuteNonQuery()
            lExito = True
        Catch x As SqlException
            MsgBox("V_ReservaADO (pr_ModifcarReserva) --> " & x.Message, MsgBoxStyle.Critical)
            lExito = False
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return lExito
    End Function

    Public Function pr_RecuperarReservaPorDNI(ByVal sDnir As String, ByVal sFchViaje As String) As Integer
        Dim nCod As Integer = 0
        Try
            cnx.Open()
            With cmd
                .Connection = cnx
                .CommandType = CommandType.Text
                .CommandText = "SELECT MAX(CodReserva) AS CodReserva FROM dbo.V_Reserva WITH (NOLOCK) INNER JOIN dbo.M_Pasajero WITH (NOLOCK) ON V_Reserva.CodPasajero = M_Pasajero.CodPasajero WHERE M_Pasajero.NroDocumentoId= @p_NroDocumentoId AND V_Reserva.FchViaje = @p_FchViaje "
                .Parameters.Add(New SqlParameter("@p_NroDocumentoId", SqlDbType.VarChar, 8))
                .Parameters("@p_NroDocumentoId").Value = sDnir
                .Parameters.Add(New SqlParameter("@p_FchViaje", SqlDbType.VarChar, 10))
                .Parameters("@p_FchViaje").Value = sFchViaje
                nCod = .ExecuteScalar()
            End With
        Catch myException As Exception
            MsgBox("V_ReservaADO (pr_RecuperarReservaPorDNI) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        Finally
            cnx.Close()
        End Try
        Return nCod
    End Function

    Public Function pr_TraerReserva(ByVal sCodr As Integer) As V_ReservaBE
        Dim dtr As SqlDataReader
        Dim _rBE As New V_ReservaBE
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "pr_TraerReserva"
            cmd.Parameters.Add(New SqlParameter("@pnCodReserva", SqlDbType.Int))
            cmd.Parameters("@pnCodReserva").Value = sCodr
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                With _rBE
                    .CodReserva = dtr("CodReserva")
                    If IsDBNull(dtr("FchReserva")) Then
                        .FchReserva = DateTime.Now
                    Else
                        .FchReserva = dtr("FchReserva")
                    End If
                    .TipPasajero = dtr("TipPasajero")
                    If IsDBNull(dtr("NomContacto")) Then
                        .NomContacto = ""
                    Else
                        .NomContacto = dtr("NomContacto")
                    End If
                    If IsDBNull(dtr("FchViaje")) Then
                        .FchViaje = DateTime.Now
                    Else
                        .FchViaje = dtr("FchViaje")
                    End If
                    .PsoPasajero = dtr("PsoPasajero")
                    .NroPrioridad = dtr("NroPrioridad")
                    .MotivoViaje = dtr("MotivoViaje")
                    .DesMotivo = dtr("DesMotivo")
                    .CodAnalisis = dtr("CodAnalisis")
                    If IsDBNull(dtr("CodComunidad")) Then
                        .CodComunidad = ""
                    Else
                        .CodComunidad = dtr("CodComunidad")
                    End If
                    .CodCentroCosto = dtr("CodCentroCosto")
                    .PsoEquipajePasajero = dtr("PsoEquipajePasajero")
                    .EstadoReserva = dtr("EstadoReserva")
                    .FlgRequisitos = dtr("FlgRequisitos")
                    If IsDBNull(dtr("FchRetorno")) Then
                        .FchRetorno = DateTime.Now
                    Else
                        .FchRetorno = dtr("FchRetorno")
                    End If
                    .FlgPermiso = dtr("FlgPermiso")
                    .CodPasajero = dtr("CodPasajero")
                    .CodLugarReserva = dtr("CodLugarReserva")
                    .NroBoleta = dtr("NroBoleta")
                    .ObsReserva = dtr("ObsReserva")
                    .PctCentroCosto = dtr("PctCentroCosto")
                    .CodCentroCosto2 = dtr("CodCentroCosto2")
                    .FlgSctrSalud = dtr("FlgSctrSalud")
                    If IsDBNull(dtr("FchSctrSalud")) Then
                        .FchSctrSalud = DateTime.Now
                    Else
                        .FchSctrSalud = dtr("FchSctrSalud")
                    End If
                    .FlgSctrPension = dtr("FlgSctrPension")
                    If IsDBNull(dtr("FchSctrPension")) Then
                        .FchSctrSalud = DateTime.Now
                    Else
                        .FchSctrSalud = dtr("FchSctrPension")
                    End If
                    .FlgExamenAltitud = dtr("FlgExamenAltitud")
                    If IsDBNull(dtr("FchExamenAltitud")) Then
                        .FchSctrSalud = DateTime.Now
                    Else
                        .FchSctrSalud = dtr("FchExamenAltitud")
                    End If
                    .FlgAsumeMarsa = dtr("FlgAsumeMarsa")
                    .FlgPrestamo = dtr("FlgPrestamo")
                    If IsDBNull(dtr("FlgResRetorno")) Then
                        .FlgResRetorno = False
                    Else
                        .FlgResRetorno = dtr("FlgResRetorno")
                    End If
                    If IsDBNull(dtr("FlgNew")) Then
                        .FlgNew = False
                    Else
                        .FlgNew = dtr("FlgNew")
                    End If
                    If IsDBNull(dtr("FlgResPasajero")) Then
                        .FlgResPasajero = False
                    Else
                        .FlgResPasajero = dtr("FlgResPasajero")
                    End If
                    If IsDBNull(dtr("CodCreadopor")) Then
                        .CodCreadopor = ""
                    Else
                        .CodCreadopor = dtr("CodCreadopor")
                    End If
                    If IsDBNull(dtr("FchCreado")) Then
                        .FchCreado = DateTime.Now
                    Else
                        .FchCreado = dtr("FchCreado")
                    End If
                    If IsDBNull(dtr("CodModificadopor")) Then
                        .CodModificadopor = ""
                    Else
                        .CodModificadopor = dtr("CodModificadopor")
                    End If
                    If IsDBNull(dtr("FchModificado")) Then
                        .FchModificado = DateTime.Now
                    Else
                        .FchModificado = dtr("FchModificado")
                    End If
                End With
            End If
        Catch x As SqlException
            MsgBox("V_ReservaADO (pr_TraerReserva) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return _rBE
    End Function
End Class
